// 个人博客后台管理-mysql服务处理

// 引入数据库模块
const db = require('../../config/mysql.js')
const utils = require("../../common/utils")


// 添加技能
const addSkill = (data) => {
    return new Promise((resolve, reject) => {
        let { imgUrl, skill, description, link = '' } = data
        let uuid = utils.generateId()
        let create_time = utils.formatDate(new Date().getTime())
        let sql = `insert into blog_skill values("${uuid}", "${skill}", "${imgUrl}", "${description}", "${link}", "${create_time}")`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("添加成功")
            return reject("添加失败")
        })
    })
}

// 删除技能
const deleteSkill = (id) => {
    return new Promise((resolve, reject) => {
        let sql = `delete from blog_skill where id = "${id}"`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("删除成功")
            return reject("删除失败")
        })
    })
}

// 修改技能
const updateSkill = (data) => {
    return new Promise((resolve, reject) => {
        let { id, skill, imgUrl, description, link = '' } = data
        let sql = `update blog_skill set skill = "${skill}", imgUrl = "${imgUrl}", description = "${description}", link = "${link}" where id = "${id}"`

        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("修改成功")
            return reject("修改失败")
        })
    })
}

// 获取技能
const getSkill = () => {
    return new Promise((resolve, reject) => {
        let sql = "select * from blog_skill"
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            utils.sortArray(result, 'create_time')
            return resolve(result)
        })
    })
}

// 添加分类
const addCategory = (data) => {
    return new Promise((resolve, reject) => {
        let { category, type } = data
        let uuid = utils.generateId()
        let create_time = utils.formatDate(new Date().getTime())
        let sql = `insert into blog_category values("${uuid}", "${category}", ${type}, "${create_time}")`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("添加成功")
            return reject("添加失败")
        })
    })
}

// 删除分类
const deleteCategory = (id, type) => {
    return new Promise((resolve, reject) => {
        const map = {
            1: 'blog_project',
            2: 'blog_article',
            3: 'blog_photo'
        }
        let dbase = map[type]
        // 判断是否有关联数据
        let un_sql = `select count(id) as uni_count from ${dbase} where category_id = "${id}"`
        db.query(un_sql, (err1, rows) => {
            if (err1) return reject(err1)
            // 有关联数据，不允许删除        
            if (rows[0].uni_count != 0) {
                return reject("该分类下存在相关数据，删除失败")
            }
            let sql = `delete from blog_category where id = "${id}" and type = ${type}`
            db.query(sql, (err, result) => {
                if (err) return reject(err)
                if (result.affectedRows == 1) return resolve("删除成功")
                return reject("删除失败")
            })
        })
    })
}

// 修改分类
const updateCategory = (data) => {
    return new Promise((resolve, reject) => {
        let { id, category, type } = data
        let sql = `update blog_category set category = "${category}" where id = "${id}" and type = ${type}`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("修改成功")
            return reject("修改失败")
        })
    })
}

// 获取分类
const getCategory = (type) => {
    return new Promise((resolve, reject) => {
        const map = {
            1: 'blog_project',
            2: 'blog_article',
            3: 'blog_photo'
        }
        let dbase = map[type]
        let sql = `select blog_category.id, blog_category.category, blog_category.type, blog_category.create_time, count(${dbase}.id) as uni_count from blog_category left join ${dbase} on blog_category.id = ${dbase}.category_id where blog_category.type = ${type} GROUP BY blog_category.id, blog_category.category, blog_category.type,blog_category.create_time`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            utils.sortArray(result, 'create_time')
            result.forEach(item => {
                item.create_time = utils.formatDate(new Date(item.create_time).getTime())
            });
            return resolve(result)
        })
    })
}

// 添加项目
const addProject = (data) => {
    return new Promise((resolve, reject) => {
        let { project_name, imgUrl, category_id, description, git_link = "", online_link = "" } = data
        let uuid = utils.generateId()
        let create_time = utils.formatDate(new Date().getTime())
        let sql = `insert into blog_project values("${uuid}", "${project_name}", "${imgUrl}", "${category_id}", "${description}", "${git_link}", "${online_link}","${create_time}")`
        
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("添加成功")
            return reject("添加失败")
        })
    })
}

// 删除项目
const deleteProject = (id) => {
    return new Promise((resolve, reject) => {
        let sql = `delete from blog_project where id = "${id}"`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("删除成功")
            return reject("删除失败")
        })
    })
}

// 修改项目
const updateProject = (data) => {
    return new Promise((resolve, reject) => {
        let { id, project_name, imgUrl, category_id, description, git_link = "", online_link = "" } = data

        let sql = `update blog_project set project_name = "${project_name}", imgUrl = "${imgUrl}", category_id = "${category_id}", description = "${description}", git_link = "${git_link}", online_link = "${online_link}" where id = "${id}"`
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            if (result.affectedRows == 1) return resolve("修改成功")
            return reject("修改失败")
        })
    })
}

// 获取项目
const getProject = (category_id, pageNum, pageSize) => {
    return new Promise((resolve, reject) => {
        let sql = ''
        let sql1 = ''
        if (category_id != '') {
            sql = `select * from blog_project where category_id = "${category_id}" limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
            sql1 = `select count(*) as total from blog_project where category_id = "${category_id}"`
        } else {
            sql = `select * from blog_project limit ${pageSize} offset ${(pageNum - 1) * pageSize}`
            sql1 = `select count(*) as total from blog_project`
        }
        db.query(sql, (err, result) => {
            if (err) return reject(err)
            db.query(sql1, (err1, result1) => {
                if (err1) return reject(err1)
                utils.sortArray(result, 'create_time', 'desc')                 
                return resolve({
                    records: result,
                    total: result1[0].total
                })
            })
        })
    })
}

module.exports = {
    addSkill,
    deleteSkill,
    updateSkill,
    getSkill,
    addCategory,
    deleteCategory,
    updateCategory,
    getCategory,
    addProject,
    deleteProject,
    updateProject,
    getProject
}